![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Table of Contents |
You had a look at the SimpleText JDBC driver developed in Chapter 10. This appendix has the complete source code for three of its main classes: SimpleTextDriver, SimpleTextConnection, and SimpleTextStatment. The SimpleTextResultSet, SimpleTextObject, and other SimpleText driver classes source code can be found on the CD-ROM or on the books Web site. The primary purpose of this appendix is to serve as a reference while you are reading or reviewing Chapter 10, as well as to detail the inner workings of a JDBC driver.
Listing B.1 SimpleTextDriver.java.
//------------------------------------------------------- // // Module: SimpleTextDriver.java // // Description: Implementation of the JDBC Driver // interface // // Author: Karl Moss // // Copyright: (C) 1996 Karl Moss. All rights reserved. // You may study, use, modify, and distribute // this example for any purpose, provided // that this copyright notice appears in // all copies. This example is provided // WITHOUT WARRANTY, either expressed or implied. //----------------------------------------------------------------------- package jdbc.SimpleText; //----------------------------------------------------------------------- // The Java SQL framework allows for multiple database drivers. // // Each driver should supply a driver class that implements // the Driver interface. // // The DriverManager will try to load as many drivers as it can // find and then for any given connection request, it will ask each // driver in turn to try to connect to the target URL. // // It is strongly recommended that each Driver class should be // small and standalone so that the Driver class can be loaded and // queried without bringing in vast quantities of supporting code. // // When a Driver object is instantiated, it should register itself // with the SQL framework by calling DriverManager.registerDriver. // // Note: Each driver must support a null constructor so it can be // instantiated by doing: // // java.sql.Driver d = Class.forName("foo.bah.Driver").newInstance(); //----------------------------------------------------------------------- // NOTE - this is an implementation of the JDBC API version 1.00 //----------------------------------------------------------------------- import java.sql.*; public class SimpleTextDriver extends SimpleTextObject implements java.sql.Driver { //------------------------------------------------------------------- // SimpleTextDriver // Constructor. Attempt to register the JDBC driver. //------------------------------------------------------------------- public SimpleTextDriver() throws SQLException { // Attempt to register this driver with the JDBC DriverManager. // If it fails, an exception will be thrown. DriverManager.registerDriver (this); } //------------------------------------------------------------------- // connect - JDBC API // // Try to make a database connection to the given URL. // The driver should return "null" if it realizes it is the wrong // kind of driver to connect to the given URL. This will be common, // as when the JDBC driver manager is asked to connect to a given // URL, it passes the URL to each loaded driver in turn. // // The driver should raise a SQLException if it is the right // driver to connect to the given URL, but has trouble connecting to // the database. // // The java.util.Properties argument can be used to pass arbitrary // string tag/value pairs as connection arguments. // Normally, at least a "user" and "password" property should be // included in the Properties. // // url The URL of the database to connect to. // // info a list of arbitrary string tag/value pairs as // connection arguments; normally, at least a "user" and // "password" property should be included. // // Returns a Connection to the URL. //------------------------------------------------------------------- public Connection connect( String url, java.util.Properties info) throws SQLException { if (traceOn()) { trace("@connect (url=" + url + ")"); } // Ensure that we can understand the given url if (!acceptsURL(url)) { return null; } // For typical JDBC drivers, it would be appropriate to check // for a secure environment before connecting, and deny access // to the driver if it is deemed to be unsecure. For the // SimpleText driver, if the environment is not secure, we will // turn it into a read-only driver. // Create a new SimpleTextConnection object SimpleTextConnection con = new SimpleTextConnection(); // Initialize the new object con.initialize (this, info); return con; } //------------------------------------------------------------------- // acceptsURL - JDBC API // // Returns true if the driver thinks that it can open a connection // to the given URL. Typically, drivers will return true if they // understand the subprotocol specified in the URL, and false if // they don't. // // url The URL of the database. // // Returns true if this driver can connect to the given URL. //------------------------------------------------------------------- public boolean acceptsURL( String url) throws SQLException { if (traceOn()) { trace("@acceptsURL (url=" + url + ")"); } boolean rc = false; // Get the subname from the url. If the url is not valid for // this driver, a null will be returned. if (getSubname(url) != null) { rc = true; } if (traceOn()) { trace(" " + rc); } return rc; } //------------------------------------------------------------------- // getPropertyInfo - JDBC API // // The getPropertyInfo method is intended to allow a generic GUI tool // to discover what properties it should prompt a human for in order // to get enough information to connect to a database. Note that // depending on the values the human has supplied so far, additional // values may become necessary, so it may be necessary to iterate // though several calls to getPropertyInfo. // // url The URL of the database to connect to. // // info A proposed list of tag/value pairs that will be sent on // connect open. // // Returns an array of DriverPropertyInfo objects describing possible // properties. This array may be an empty array if no // properties are required. //------------------------------------------------------------------- public DriverPropertyInfo[] getPropertyInfo( String url, java.util.Properties info) throws SQLException { DriverPropertyInfo prop[]; // Only one property required for the SimpleText driver; the // directory. Check the property list coming in. If the // directory is specified, return an empty list. if (info.getProperty("Directory") == null) { // Setup the DriverPropertyInfo entry prop = new DriverPropertyInfo[1]; prop[0] = new DriverPropertyInfo("Directory", null); prop[0].description = "Initial text file directory"; prop[0].required = false; } else { // Create an empty list prop = new DriverPropertyInfo[0]; } return prop; } // ------------------------------------------------------------------ // getMajorVersion - JDBC API // // Get the driver's major version number. Initially this should be 1. // ------------------------------------------------------------------ public int getMajorVersion() { return SimpleTextDefine.MAJOR_VERSION; } // ------------------------------------------------------------------ // getMinorVersion - JDBC API // // Get the driver's minor version number. Initially this should be 0. // ------------------------------------------------------------------ public int getMinorVersion() { return SimpleTextDefine.MINOR_VERSION; } // ------------------------------------------------------------------ // jdbcCompliant - JDBC API // // Report whether the Driver is a genuine JDBC COMPLIANT (tm) driver. // A driver may only report "true" here if it passes the JDBC // compliance tests, otherwise it is required to return false. // // JDBC compliance requires full support for the JDBC API and full // support for SQL-92 Entry Level. It is expected that JDBC // compliant drivers will be available for all the major commercial // databases. // // This method is not intended to encourage the development of non- // JDBC compliant drivers, but is a recognition of the fact that some // vendors are interested in using the JDBC API and framework for // lightweight databases that do not support full database // functionality, or for special databases, such as document // information retrieval, where a SQL implementation may not be // feasible. // ------------------------------------------------------------------ public boolean jdbcCompliant() { // The SimpleText driver is not JDBC compliant return false; } // ------------------------------------------------------------------ // getSubname // Given a URL, return the subname. Returns null if the protocol is // not 'jdbc' or the subprotocol is not 'simpletext'. // ------------------------------------------------------------------ public String getSubname( String url) { String subname = null; String protocol = "JDBC"; String subProtocol = "SIMPLETEXT"; // Convert to upper case and trim all leading and trailing // blanks. url = (url.toUpperCase()).trim(); // Make sure the protocol is jdbc: if (url.startsWith(protocol)) { // Strip off the protocol url = url.substring (protocol.length()); // Look for the colon if (url.startsWith(":")) { url = url.substring(1); // Check the subprotocol if (url.startsWith (subProtocol)) { // Strip off the subprotocol, leaving the subname url = url.substring(subProtocol.length()); // Look for the colon that separates the subname // from the subprotocol (or the fact that there // is no subprotocol at all). if (url.startsWith(":")) { subname = url.substring(subProtocol.length()); } else if (url.length() == 0) { subname = ""; } } } } return subname; } }
Listing B.2 SimpleTextConnection.java.
// ---------------------------------------------------------------------- // // Module: SimpleTextConnection.java // // Description: Implementation of the JDBC Connection interface // // Author: Karl Moss // // Copyright: (C) 1996 Karl Moss. All rights reserved. // You may study, use, modify, and distribute this example // for any purpose, provided that this copyright notice // appears in all copies. This example is provided WITHOUT // WARRANTY, either expressed or implied. // ---------------------------------------------------------------------- package jdbc.SimpleText; // ---------------------------------------------------------------------- // A Connection represents a session with a specific // database. Within the context of a Connection, SQL statements are // executed and results are returned. // // A Connection's database is able to provide information // describing its tables, its supported SQL grammar, its stored // procedures, the capabilities of this connection, etc. This // information is obtained with the getMetaData method. // // Note: By default, the Connection automatically commits // changes after executing each statement. If auto commit has been // disabled, an explicit commit must be done or database changes will // not be saved. // ---------------------------------------------------------------------- // NOTE - this is an implementation of the JDBC API version 1.00 // ---------------------------------------------------------------------- import java.sql.*; import java.io.*; import java.util.Hashtable; import java.util.StringTokenizer; public class SimpleTextConnection extends SimpleTextObject implements SimpleTextIConnection { //------------------------------------------------------------------- // initialize // Initialize the Connection object. //------------------------------------------------------------------- public void initialize ( Driver driver, java.util.Properties info) throws SQLException { // Save the owning driver object ownerDriver = driver; // Get the security manager and see if we can write to a file. // If no security manager is present, assume that we are a // trusted application and have read/write privileges. canWrite = false; SecurityManager securityManager = System.getSecurityManager (); if (securityManager != null) { try { // Use some arbitrary file to check for file write // privileges. securityManager.checkWrite ("SimpleText_Foo"); // Flag is set if no exception is thrown canWrite = true; } // If we can't write, an exception is thrown. We'll catch // it and do nothing. catch (SecurityException ex) { } } else { canWrite = true; } // Set our initial read-only flag setReadOnly(!canWrite); // Get the directory. It will either be supplied in the property // list, or we'll use our current default. String s = info.getProperty("Directory"); if (s == null) { s = System.getProperty("user.dir"); } setCatalog(s); } // ------------------------------------------------------------------ // createStatement - JDBC API // // SQL statements without parameters are normally // executed using Statement objects. If the same SQL statement // is executed many times, it is more efficient to use a // PreparedStatement. // // Returns a new Statement object. // ------------------------------------------------------------------ public Statement createStatement() throws SQLException { if (traceOn()) { trace("Creating new SimpleTextStatement"); } // Create a new Statement object SimpleTextStatement stmt = new SimpleTextStatement(); // Initialize the statement stmt.initialize (this); return stmt; } // ------------------------------------------------------------------ // prepareStatement - JDBC API // // A SQL statement with or without IN parameters can be // pre-compiled and stored in a PreparedStatement object. This // object can then be used to efficiently execute this statement // multiple times. // // Note: This method is optimized for handling // parametric SQL statements that benefit from precompilation. If // the driver supports precompilation, prepareStatement will send // the statement to the database for precompilation. Some drivers // may not support precompilation. In this case, the statement may // not be sent to the database until the PreparedStatement is // executed. This has no direct affect on users; however, it does // affect which method throws certain SQLExceptions. // // sql a SQL statement that may contain one or more '?' IN // parameter placeholders. // // Returns a new PreparedStatement object containing the // pre-compiled statement. // ------------------------------------------------------------------ public PreparedStatement prepareStatement( String sql) throws SQLException { if (traceOn()) { trace("@prepareStatement (sql=" + sql + ")"); } // Create a new PreparedStatement object SimpleTextPreparedStatement ps = new SimpleTextPreparedStatement(); // Initialize the PreparedStatement ps.initialize(this, sql); return ps; } // ------------------------------------------------------------------ // prepareCall - JDBC API // // A SQL stored procedure call statement is handled by creating a // CallableStatement for it. The CallableStatement provides // methods for setting up its IN and OUT parameters, and // methods for executing it. // // Note: This method is optimized for handling stored // procedure call statements. Some drivers may send the call // statement to the database when the prepareCall is done; others // may wait until the CallableStatement is executed. This has no // direct affect on users; however, it does affect which method // throws certain SQLExceptions. // // sql a SQL statement that may contain one or more '?' // parameter placeholders. // // Returns a new CallableStatement object containing the // pre-compiled SQL statement. // ------------------------------------------------------------------ public CallableStatement prepareCall( String sql) throws SQLException { if (traceOn()) { trace("@prepareCall (sql=" + sql + ")"); } // The SimpleText driver does not support callable statements throw new SQLException("Driver does not support this function"); } // ------------------------------------------------------------------ // nativeSQL - JDBC API // // A driver may convert the JDBC sql grammar into its system's // native SQL grammar prior to sending it; nativeSQL returns the // native form of the statement that the driver would have sent. // // sql a SQL statement that may contain one or more '?' // parameter placeholders. // // Returns the native form of this statement. // ------------------------------------------------------------------ public String nativeSQL( String sql) throws SQLException { // For the SimpleText driver, simply return the original // sql statement. Other drivers will need to expand escape // sequences here. return sql; } // ------------------------------------------------------------------ // setAutoCommit - JDBC API // // If a connection is in auto-commit mode, then all its SQL // statements will be executed and committed as individual // transactions. Otherwise, its SQL statements are grouped into // transactions that are terminated by either commit() or // rollback(). By default, new connections are in auto-commit // mode. // // autoCommit true enables auto-commit; false disables // auto-commit. // ------------------------------------------------------------------ public void setAutoCommit( boolean autoCommit) throws SQLException { if (traceOn()) { trace("@setAutoCommit (autoCommit=" + autoCommit + ")"); } // The SimpleText driver is always in auto-commit mode (it does // not support transactions). Throw an exception if an attempt // is made to change the mode. if (autoCommit == false) { throw DriverNotCapable(); } } // ------------------------------------------------------------------ // getAutoCommit - JDBC API // // Get the current auto-commit state. // Returns the current state of auto-commit mode. // ------------------------------------------------------------------ public boolean getAutoCommit() throws SQLException { // The SimpleText driver is always in auto-commit mode (it does // not support transactions). return true; } // ------------------------------------------------------------------ // commit - JDBC API // // Commit makes all changes made since the previous // commit/rollback permanent and releases any database locks // currently held by the Connection. // ------------------------------------------------------------------ public void commit() throws SQLException { // No-op for the SimpleText driver } // ------------------------------------------------------------------ // rollback - JDBC API // // Rollback drops all changes made since the previous // commit/rollback and releases any database locks currently held // by the Connection. // ------------------------------------------------------------------ public void rollback() throws SQLException { // No-op for the SimpleText driver } // ------------------------------------------------------------------ // close - JDBC API // // In some cases, it is desirable to immediately release a // Connection's database and JDBC resources instead of waiting for // them to be automatically released; the close method provides this // immediate release. // ------------------------------------------------------------------------ public void close() throws SQLException { connectionClosed = true; } // ------------------------------------------------------------------ // isClosed - JDBC API // // Check if a Connection is closed. // ------------------------------------------------------------------ public boolean isClosed() throws SQLException { return connectionClosed; } // ------------------------------------------------------------------ // getMetaData - JDBC API // // A Connection's database is able to provide information // describing its tables, its supported SQL grammar, its stored // procedures, the capabilities of this connection, etc. This // information is made available through a DatabaseMetaData // object. // // Returns a DatabaseMetaData object for this Connection. // ------------------------------------------------------------------ public DatabaseMetaData getMetaData() throws SQLException { SimpleTextDatabaseMetaData dbmd = new SimpleTextDatabaseMetaData (); dbmd.initialize(this); return dbmd; } // ------------------------------------------------------------------ // setReadOnly - JDBC API // // You can put a connection in read-only mode as a hint to enable // database optimizations. // // Note: setReadOnly cannot be called while in the // middle of a transaction. // ------------------------------------------------------------------ public void setReadOnly( boolean readOnly) throws SQLException. { // If we are trying to set the connection not read only (allowing // writes), and this connection does not allow writes, throw // an exception. if ((readOnly == false) && (canWrite == false)) { throw DriverNotCapable(); } // Set the readOnly attribute for the SimpleText driver. If set, // the driver will not allow updates or deletes to any text file. this.readOnly = readOnly; } // ------------------------------------------------------------------ // isReadOnly - JDBC API // // Test if the connection is in read-only mode. // ------------------------------------------------------------------ public boolean isReadOnly() throws SQLException { return readOnly; } // ------------------------------------------------------------------ // setCatalog - JDBC API // // A sub-space of this Connection's database may be selected by // setting a catalog name. If the driver does not support catalogs, it // will silently ignore this request. // ------------------------------------------------------------------ public void setCatalog(String catalog) throws SQLException { if (traceOn()) { trace("@setCatalog(" + catalog + ")"); } // If the last character is a separator, remove it if (catalog.endsWith("/") || catalog.endsWith("\\")) { catalog = catalog.substring(0, catalog.length()); } // Make sure this is a directory File dir = new File(catalog); if (!dir.isDirectory()) { throw new SQLException("Invalid directory: " + catalog); } this.catalog = catalog; } // ------------------------------------------------------------------ // getCatalog // Returns the Connection's current catalog name. // ------------------------------------------------------------------ public String getCatalog() throws SQLException { return catalog; } // ------------------------------------------------------------------ // setTransactionIsolation - JDBC API // // You can call this method to try to change the transaction // isolation level on a newly opened connection, using one of the // TRANSACTION_* values. // // level one of the TRANSACTION_* isolation values with the // exception of TRANSACTION_NONE; some databases may not // support other values. // ------------------------------------------------------------------ public void setTransactionIsolation( int level) throws SQLException { if (traceOn()) { trace("@setTransactionIsolation (level=" + level + ")"); } // Throw an exception if the transaction isolation is being // changed to something different. if (level != TRANSACTION_NONE) { throw DriverNotCapable(); } } // ------------------------------------------------------------------ // getTransactionIsolation - JDBC API // // Get this Connection's current transaction isolation mode // ------------------------------------------------------------------ public int getTransactionIsolation() throws SQLException { // The SimpleText driver does not support transactions return TRANSACTION_NONE; } // ------------------------------------------------------------------ // setAutoClose - JDBC API // // When a Connection is in auto-close mode, all its // PreparedStatements, CallableStatements, and ResultSets will be // closed when a transaction is committed or rolled back. By // default, a new Connection is in auto-close mode. // // When auto-close is disabled, JDBC attempts to keep // all statements and ResultSets open across commits and // rollbacks. However, the actual behaviour will vary depending // on what the underlying database supports. Some databases // allow these objects to remain open across commits, whereas // other databases insist on closing them. // // autoClose true enables auto-close, false disables // auto-close. // ------------------------------------------------------------------ public void setAutoClose( boolean autoClose) throws SQLException { if (traceOn()) { trace("@setAutoClose (autoClose=" + autoClose + ")"); } // If autoClose is being turned off, throw an exception; we can't // handle it. if (autoClose == false) { throw DriverNotCapable(); } } // ------------------------------------------------------------------ // getAutoClose - JDBC API // // Gets the current auto-close state. // ------------------------------------------------------------------ public boolean getAutoClose() throws SQLException { // Always true for the SimpleText driver return true; } // ------------------------------------------------------------------ // getWarnings - JDBC API // // The first warning reported by calls on this Connection is // returned. // // Note: Subsequent warnings will be chained to this SQLWarning. // ------------------------------------------------------------------ public SQLWarning getWarnings() throws SQLException { // No warnings exist for the SimpleText driver. Always return // null. return null; } // ------------------------------------------------------------------ // clearWarnings - JDBC API // // After this call, getWarnings returns null until a new warning is // reported for this Connection. // ------------------------------------------------------------------ public void clearWarnings() throws SQLException { // No-op } //------------------------------------------------------------------- // parseSQL // Given a sql statement, parse it and return a String array with // each keyword. This is a VERY simple parser. // ------------------------------------------------------------------ public String[] parseSQL( String sql) { String keywords[] = null; // Create a new Hashtable to keep our words in. This way, we can // build the Hashtable as we go, then create a String array // once we know how many words are present. java.util.Hashtable table = new java.util.Hashtable(); int count = 0; // Current offset in the sql string int offset = 0; // Get the first word from the sql statement String word = parseWord(sql.substring(offset)); // Loop while more words exist in the sql string while (word.length() > 0) { // Increment the offset pointer offset += word.length(); // Trim all leading and trailing spaces word = word.trim(); if (word.length() > 0) { // Put the word in our hashtable table.put(new Integer(count), word); count++; } // Get the next word word = parseWord(sql.substring(offset)); } // Create our new String array with the proper number of elements keywords = new String[count]; // Copy the words from the Hashtable to the String array for (int i = 0; i < count; i++) { keywords[i] = (String) table.get(new Integer(i)); } return keywords; } // ------------------------------------------------------------------ // getTables // Given a directory and table pattern, return a Hashtable containing // SimpleTextTable entries. // ------------------------------------------------------------------ public Hashtable getTables( String dir, String table) { Hashtable list = new Hashtable(); // Create a FilenameFilter object. This object will only allow // files with the .SDF extension to be seen. FilenameFilter filter = new SimpleTextEndsWith( SimpleTextDefine.DATA_FILE_EXT); File file = new File(dir); if (file.isDirectory()) { // List all of the files in the directory with the .SDF // extension. String entries[] = file.list(filter); SimpleTextTable tableEntry; // Create a SimpleTextTable entry for each, and put in // the Hashtable. for (int i = 0; i < entries.length; i++) { // A complete driver needs to further filter the table // name here. tableEntry = new SimpleTextTable(dir, entries[i]); list.put(new Integer(i), tableEntry); } } return list; } // ------------------------------------------------------------------ // getColumns // Given a directory and table name, return a Hashtable containing // SimpleTextColumn entries. Returns null if the table is not found. // ------------------------------------------------------------------ public Hashtable getColumns( String dir, String table) { Hashtable list = new Hashtable(); // Create the full path to the table String fullPath = dir + "/" + table + SimpleTextDefine.DATA_FILE_EXT; File f = new File (fullPath); // If the file does not exist, return null if (!f.exists()) { if (traceOn()) { trace("File does not exist: " + fullPath); } return null; } String line = ""; // Create a random access object and read the first line. // Create the table. try { RandomAccessFile raf = new RandomAccessFile(f, "r"); // Read the first line, which is the column definitions line = raf.readLine(); } catch (IOException ex) { if (traceOn()) { trace("Unable to read file: " + fullPath); } return null; } // Now, parse the line. First, check for the branding if (!line.startsWith(SimpleTextDefine.DATA_FILE_EXT)) { if (traceOn()) { trace("Invalid file format: " + fullPath); } return null; } line = line.substring(SimpleTextDefine.DATA_FILE_EXT.length()); // Now we can use the StringTokenizer, since we know that the // column names can't contain data within quotes (this is why // we can't use the StringTokenizer with SQL statements). StringTokenizer st = new StringTokenizer(line, ","); String columnName; int columnType; int precision; SimpleTextColumn column; int count = 0; boolean searchable; int displaySize; String typeName; // Loop while more tokens exist while (st.hasMoreTokens()) { columnName = (st.nextToken()).trim(); if (columnName.length() == 0) { continue; } if (columnName.startsWith(SimpleTextDefine.COL_TYPE_NUMBER)) { columnType = Types.INTEGER; precision = SimpleTextDefine.MAX_INTEGER_LEN; columnName = columnName.substring( SimpleTextDefine.COL_TYPE_NUMBER.length()); displaySize = precision; typeName = "VARCHAR"; searchable = true; } else if (columnName.startsWith(SimpleTextDefine. COL_TYPE_BINARY)) { columnType = Types.VARBINARY; precision = SimpleTextDefine.MAX_VARBINARY_LEN; columnName = columnName.substring( SimpleTextDefine.COL_TYPE_BINARY.length()); displaySize = precision * 2; typeName = "BINARY"; searchable = false; } else { columnType = Types.VARCHAR; precision = SimpleTextDefine.MAX_VARCHAR_LEN; searchable = true; displaySize = precision; typeName = "NUMBER"; } // Create a new column object and add to the Hashtable column = new SimpleTextColumn(columnName, columnType, precision); column.searchable = searchable; column.displaySize = displaySize; column.typeName = typeName; // The column number will be 1-based count++; // Save the absolute column number column.colNo = count; list.put(new Integer(count), column); } return list; } // ------------------------------------------------------------------ // getDirectory // Given a directory filter (which may be null), format the directory // to use in a search. The default connection directory may be // returned. // ------------------------------------------------------------------ public String getDirectory( String directory) { String dir; if (directory == null) { dir = catalog; } else if (directory.length() == 0) { dir = catalog; } else { dir = directory; if (dir.endsWith("/") || dir.endsWith("\\")) { dir = dir.substring(0, dir.length()); } } return dir; } protected Driver ownerDriver; // Pointer to the owning // Driver object protected boolean connectionClosed; // True if the connection // is currently closed protected boolean readOnly; // True if the connection // is read-only protected boolean canWrite; // True if we are able to // write to files protected String catalog; // Current catalog // (qualifier) for text files } // ---------------------------------------------------------------------- // This class is a simple FilenameFilter. It defines the required // accept() method to determine whether a specified file should be listed. // A file will be listed if its name ends with the specified extension. // ---------------------------------------------------------------------- class SimpleTextEndsWith implements FilenameFilter { public SimpleTextEndsWith( String extension) { ext = extension; } public boolean accept( File dir, String name) { if (name.endsWith(ext)) { return true; } return false; } protected String ext; }
Listing B.3 SimpleTextStatement.java.
// ---------------------------------------------------------------------- // // Module: SimpleTextStatement.java // // Description: Implementation of the JDBC Statement interface // // Author: Karl Moss // // Copyright: (C) 1996 Karl Moss. All rights reserved. // You may study, use, modify, and distribute this example // for any purpose, provided that this copyright notice // appears in all copies. This example is provided WITHOUT // WARRANTY, either expressed or implied. // ---------------------------------------------------------------------- package jdbc.SimpleText; // ---------------------------------------------------------------------- // A Statement object is used for executing a static SQL statement // and obtaining the results produced by it. // // Only one ResultSet per Statement can be open at any point in // time. Therefore, if the reading of one ResultSet is interleaved with // the reading of another, each must have been generated by different // Statements. // ---------------------------------------------------------------------- // NOTE - this is an implementation of the JDBC API version 1.00 // ---------------------------------------------------------------------- import java.sql.*; import java.util.Hashtable; import java.io.*; public class SimpleTextStatement extends SimpleTextObject implements SimpleTextIStatement { // ------------------------------------------------------------------ // initialize // ------------------------------------------------------------------ public void initialize( SimpleTextIConnection con) throws SQLException { // Save the owning connection object ownerConnection = con; } // ------------------------------------------------------------------ // executeQuery - JDBC API // // Execute a SQL statement that returns a single ResultSet. // // sql typically this is a static SQL SELECT statement. // // Returns the table of data produced by the SQL statement. // ------------------------------------------------------------------ public ResultSet executeQuery( String sql) throws SQLException { if (traceOn()) { trace("@executeQuery(" + sql + ")"); } java.sql.ResultSet rs = null; // Execute the query. If execute returns true, then a result set // exists. if (execute(sql)) { rs = getResultSet(); } return rs; } // ------------------------------------------------------------------ // executeUpdate - JDBC API // // Execute a SQL INSERT, UPDATE, or DELETE statement. In addition, // SQL statements that return nothing, such as SQL DDL statements, // can be executed. // // sql a SQL INSERT, UPDATE, or DELETE statement or a SQL // statement that returns nothing. // // Returns either the row count for INSERT, UPDATE, or DELETE; or 0 // for SQL statements that return nothing. // ------------------------------------------------------------------ public int executeUpdate( String sql) throws SQLException { if (traceOn()) { trace("@executeUpdate(" + sql + ")"); } int count = -1; // Execute the query. If execute returns false, then an update // count exists. if (execute(sql) == false) { count = getUpdateCount(); } return count; } // ------------------------------------------------------------------ // close - JDBC API // // In many cases, it is desirable to immediately release a // Statements' database and JDBC resources instead of waiting for // this to happen when it is automatically closed; the close // method provides this immediate release. // // Note: A Statement is automatically closed when it is // garbage collected. When a Statement is closed, its current // ResultSet, if one exists, is also closed. // ------------------------------------------------------------------ public void close() throws SQLException { // If we have a current result set, close it if (currentResultSet != null) { currentResultSet.close(); currentResultSet = null; } } // ------------------------------------------------------------------ // getMaxFieldSize - JDBC API // // The maxFieldSize limit (in bytes) is the maximum amount of data // returned for any column value; it only applies to BINARY, // VARBINARY, LONGVARBINARY, CHAR, VARCHAR, and LONGVARCHAR // columns. If the limit is exceeded, the excess data is silently // discarded. // // Returns the current max column size limit; zero means unlimited. // ------------------------------------------------------------------ public int getMaxFieldSize() throws SQLException { // The SimpleText driver does not have a limit on size return 0; } // ------------------------------------------------------------------ // setMaxFieldSize - JDBC API // // The maxFieldSize limit (in bytes) is set to limit the size of // data that can be returned for any column value; it only applies // to BINARY, VARBINARY, LONGVARBINARY, CHAR, VARCHAR, and // LONGVARCHAR fields. If the limit is exceeded, the excess data // is silently discarded. // // max the new max column size limit; zero means unlimited. // ------------------------------------------------------------------ public void setMaxFieldSize( int max) throws SQLException { // The SimpleText driver does not allow the maximum field size to // be set. if (max != 0) { throw DriverNotCapable(); } } // ------------------------------------------------------------------ // getMaxRows - JDBC API // // The maxRows limit is the maximum number of rows that a // ResultSet can contain. If the limit is exceeded, the excess // rows are silently dropped. // // Returns the current max row limit; zero means unlimited. // ------------------------------------------------------------------ public int getMaxRows() throws SQLException { // The SimpleText driver does not have a limit on the number // of rows that can be returned. return 0; } // ------------------------------------------------------------------ // setMaxRows - JDBC API // // The maxRows limit is set to limit the number of rows that any // ResultSet can contain. If the limit is exceeded, the excess // rows are silently dropped. // // max the new max rows limit; zero means unlimited. // ------------------------------------------------------------------ public void setMaxRows( int max) throws SQLException { // The SimpleText driver does not allow the maximum number of // rows to be set. if (max != 0) { throw DriverNotCapable(); } } // ------------------------------------------------------------------ // setEscapeProcessing - JDBC API // // If escape scanning is on (the default), the driver will do // escape substitution before sending the SQL to the database. // // enable true to enable; false to disable. // ------------------------------------------------------------------ public void setEscapeProcessing( boolean enable) throws SQLException { // The SimpleText driver does not support escape sequence // expansion. if (enable) { throw DriverNotCapable(); } } // ------------------------------------------------------------------ // getQueryTimeout - JDBC API // // The queryTimeout limit is the number of seconds the driver will // wait for a Statement to execute. If the limit is exceeded, a // SQLException is thrown. // // Returns the current query timeout limit in seconds; zero means // unlimited. // ------------------------------------------------------------------ public int getQueryTimeout() throws SQLException { // The SimpleText driver does not have a query timeout return 0; } // ------------------------------------------------------------------ // setQueryTimeout - JDBC API // // The queryTimeout limit is the number of seconds the driver will // wait for a Statement to execute. If the limit is exceeded, a // SQLException is thrown. // // seconds the new query timeout limit in seconds; zero means // unlimited. // ------------------------------------------------------------------ public void setQueryTimeout( int seconds) throws SQLException { // The SimpleText driver does not support query timeouts if (seconds != 0) { throw DriverNotCapable(); } } // ------------------------------------------------------------------ // cancel - JDBC API // // Cancel can be used by one thread to cancel a statement that // is being executed by another thread. // ------------------------------------------------------------------ public void cancel() throws SQLException { // No-op for the SimpleText driver } // ------------------------------------------------------------------ // getWarnings - JDBC API // // The first warning reported by calls on this Statement is returned. // A Statment's execute methods clear its SQLWarning chain. // Subsequent Statement warnings will be chained to this SQLWarning. // // Note: The warning chain is automatically cleared each time // a statement is (re)executed. // // Note: If you are processing a ResultSet, then any // warnings associated with ResultSet reads will be chained on the // ResultSet object. // // Returns the first SQLWarning or null. // ------------------------------------------------------------------ public SQLWarning getWarnings() throws SQLException { return lastWarning; } // ------------------------------------------------------------------ // clearWarnings - JDBC API // // After this call, getWarnings returns null until a new warning is // reported for this Statement. // ------------------------------------------------------------------ public void clearWarnings() throws SQLException { setWarning(null); } // ------------------------------------------------------------------ // setWarning // Sets the given SQLWarning in the warning chain. If null, the // chain is reset. // ------------------------------------------------------------------ protected void setWarning( SQLWarning warning) { if (warning == null) { lastWarning = null; } else { SQLWarning chain = lastWarning; // Find the end of the chain while (chain.getNextWarning() != null) { chain = chain.getNextWarning(); } // We're at the end of the chain. Add the new warning chain.setNextWarning(warning); } } // ------------------------------------------------------------------ // setCursorName - JDBC API // // setCursorname defines the SQL cursor name that will be used by // subsequent Statement execute methods. This name can then be // used in SQL positioned update/delete statements to identify the // current row in the ResultSet generated by this statement. If // the database doesn't support positioned update/delete, this // method is a no-op. // // Note: By definition, positioned update/delete // execution must be done by a different Statement than the one // which generated the ResultSet being used for positioning. Also, // cursor names must be unique within a Connection. // // name the new cursor name. // ------------------------------------------------------------------ public void setCursorName( String name) throws SQLException { // The SimpleText driver does not support positioned updates. // Per the spec, this is a no-op. } // ------------------------------------------------------------------ // execute - JDBC API // // Execute a SQL statement that may return multiple results. // Under some (uncommon) situations, a single SQL statement may return // multiple result sets and/or update counts. Normally, you can // ignore this, unless you're executing a stored procedure that you // know may return multiple results, or unless you're dynamically // executing an unknown SQL string. The "execute", "getMoreResults", // "getResultSet"and "getUpdateCount" methods let you navigate // through multiple results. // // The "execute" method executes a SQL statement and indicates the // form of the first result. You can then use getResultSet or // getUpdateCount to retrieve the result, and getMoreResults to // move to any subsequent result(s). // // sql any SQL statement. // // Returns true if the first result is a ResultSet; false if it is an // integer. // ------------------------------------------------------------------ public boolean execute( String sql) throws SQLException { resultSetColumns = null; // Convert the SQL statement into native syntax sql = ownerConnection.nativeSQL(sql); // Save the SQL statement sqlStatement = sql; // First, parse the sql statement into a String array parsedSQL = ownerConnection.parseSQL(sql); // Now, validate the SQL statement and execute it. // Returns true if a result set exists. boolean rc = prepare(false); return rc; } // ------------------------------------------------------------------ // getResultSet - JDBC API // // Returns the current result as a ResultSet. It // should only be called once per result. // // Returns the current result as a ResultSet; null if it is an // integer. // ------------------------------------------------------------------ public ResultSet getResultSet() throws SQLException { // If there are no columns to be returned, return null if (resultSetColumns == null) { return null; } SimpleTextResultSet rs = new SimpleTextResultSet(); rs.initialize(this, resultSetCatalog, resultSetTable, resultSetColumns, resultSetFilter); // Save our current result set currentResultSet = rs; return rs; } // ------------------------------------------------------------------ // getUpdateCount - JDBC API // // getUpdateCount returns the current result, which should be an // integer value. It should only be called once per result. // // The only way to tell for sure that the result is an update // count is to first test to see if it is a ResultSet. If it is // not a ResultSet, it is an update count. // // Returns the current result as an integer; zero if it is a // ResultSet. // ------------------------------------------------------------------ public int getUpdateCount() throws SQLException { return updateCount; } // ------------------------------------------------------------------ // getMoreResults - JDBC API // // getMoreResults moves to a Statement's next result. It returns // true if this result is a ResultSet. getMoreResults also // implicitly closes any current ResultSet obtained with // getResultSet. // // Returns true if the next result is a ResultSet; false if it is an // integer. // ------------------------------------------------------------------ public boolean getMoreResults() throws SQLException { // The SimpleText driver does not support multiple result sets throw DriverNotCapable(); } // ------------------------------------------------------------------ // getStatementType // Given a parsed SQL statement (in a String array), determine the // type of sql statement present. If the sql statement is not known, // an exception is raised. // ------------------------------------------------------------------ public int getStatementType( String sql[]) throws SQLException { int type = 0; // There are no sql statements with less than 2 words if (sql.length < 2) { throw new SQLException("Invalid SQL statement"); } if (sql[0].equalsIgnoreCase("SELECT")) { type = SimpleTextDefine.SQL_SELECT; } else if (sql[0].equalsIgnoreCase("INSERT")) { type = SimpleTextDefine.SQL_INSERT; } else if (sql[0].equalsIgnoreCase("CREATE")) { type = SimpleTextDefine.SQL_CREATE; } else if (sql[0].equalsIgnoreCase("DROP")) { type = SimpleTextDefine.SQL_DROP; } else { throw new SQLException("Invalid SQL statement: " + sql[0]); } return type; } // ------------------------------------------------------------------ // prepare // Prepare the already parsed SQL statement. // Returns true if a result set exists. // ------------------------------------------------------------------ protected boolean prepare( boolean prepareOnly) throws SQLException { boolean resultSet = false; // Determine the type of statement present statementType = getStatementType(parsedSQL); // Perform action depending upon the SQL statement type switch (statementType) { // CREATE statement case SimpleTextDefine.SQL_CREATE: // If attempting to prepare a DDL (Data Definition Language) // statement, raise an exception. if (prepareOnly) { throw new SQLException("DDL statements cannot be prepared"); } // Create the table createTable(); updateCount = 0; break; // DROP statement case SimpleTextDefine.SQL_DROP: // If attempting to prepare a DDL (Data Definition Language) // statement, raise an exception if (prepareOnly) { throw new SQLException("DDL statements cannot be prepared"); } // Drop the table dropTable(); updateCount = 0; break; // INSERT statement case SimpleTextDefine.SQL_INSERT: // Insert data into the table insert(prepareOnly); updateCount = 1; break; // SELECT statement case SimpleTextDefine.SQL_SELECT: // Select data from the table select(prepareOnly); resultSet = true; updateCount = -1; break; default: throw new SQLException("Unknown SQL statement type: " + statementType); } return resultSet; } // ------------------------------------------------------------------ // createTable // Attempt to create the table from the parsed SQL statement. // // Grammar: // // create-statement ::= CREATE TABLE table-name // (column-element [,column-element] ...) // // column-element ::= column-identifier data-type // // ------------------------------------------------------------------ protected void createTable() throws SQLException { // The minimum SQL statement must have 7 elements: // // CREATE TABLE foo (COL VARCHAR) if (parsedSQL.length < 7) { throw new SQLException ("Invalid CREATE statement"); } // The next word must be TABLE; this is the only type of // CREATE that the SimpleText driver supports. if (!parsedSQL[1].equalsIgnoreCase("TABLE")) { throw new SQLException("CREATE must be followed by TABLE"); } // Make sure we are not in read-only mode if (ownerConnection.isReadOnly()) { throw new SQLException( "Unable to CREATE TABLE: connection is read-only"); } // The next word is the table name. Verify that it does not // contain any invalid characters. validateName(parsedSQL[2], "table"); // The next word should be an open paren if (!parsedSQL[3].equals("(")) { throw new SQLException( "Invalid CREATE TABLE statement: missing paren '('"); } // Now we can step through the other parameters. The format // should be: // // ( column type [, column type] ... ) // // We will build a text line that describes each of the columns. // This line will be the first line in our simple text file. // // Numeric column names start with '#' // Binary column names start with '@' // All other names are considered to be varchar String line = ""; String columnName; String typeName; int word = 4; boolean gotCloseParen = false; int numCols = 0; boolean hasBinary = false; // Keep a Hashtable of all of the column names so we can check // for duplicates. Hashtable names = new Hashtable(); while ((word < parsedSQL.length) && (!gotCloseParen)) { // Get the column name to create and validate columnName = parsedSQL[word].toUpperCase(); validateName(columnName, "column"); if (names.get(columnName) != null) { throw new SQLException("Duplicate column name: " + columnName); } names.put(columnName, ""); word++; // The next column should be the type if (word == parsedSQL.length) { throw new SQLException("Missing column type"); } typeName = parsedSQL[word]; if (numCols > 0) { line += ","; } numCols++; // Validate the type if (typeName.equalsIgnoreCase("VARCHAR")) { line += columnName; } else if (typeName.equalsIgnoreCase("NUMBER")) { line += SimpleTextDefine.COL_TYPE_NUMBER + columnName; } else if (typeName.equalsIgnoreCase("BINARY")) { line += SimpleTextDefine.COL_TYPE_BINARY + columnName; hasBinary = true; } else { throw new SQLException("Invalid column type: " + typeName); } word++; if (word == parsedSQL.length) { throw new SQLException("Missing close paren"); } // The next word must either be a comma, indicating more // columns, or the closing paren. if (parsedSQL[word].equals(")")) { gotCloseParen = true; word++; break; } else if (!parsedSQL[word].equals(",")) { throw new SQLException("Invalid character near: " + columnName + " " + typeName); } word++; } // If we got here and did not find a closing paren, raise an // error. if (!gotCloseParen) { throw new SQLException("Missing close paren"); } // We could check for extra junk at the end of the statement, but // we'll just ignore it. // Verify that the file does not already exist String fileName = parsedSQL[2].toUpperCase(); String fullFile = fileName + SimpleTextDefine.DATA_FILE_EXT; String fullPath = ownerConnection.getCatalog() + "/" + fullFile; File f = new File (fullPath); if (f.exists()) { throw new SQLException("Table already exists: " + fileName); } // Create the table try { RandomAccessFile raf = new RandomAccessFile(f, "rw"); // Brand the file raf.writeBytes(SimpleTextDefine.DATA_FILE_EXT); // Write the column info raf.writeBytes(line); raf.writeBytes("\n"); raf.close(); } catch (IOException ex) { throw new SQLException("Error accessing file " + fullPath + ": " + ex.getMessage()); } // If a binary data type existed, create the binary data file now fullFile = fileName + SimpleTextDefine.BINARY_FILE_EXT; fullPath = ownerConnection.getCatalog() + "/" + fullFile; f = new File (fullPath); // Create the binary table try { RandomAccessFile raf = new RandomAccessFile(f, "rw"); raf.close(); } catch (IOException ex) { throw new SQLException("Error accessing file " + fullPath + ": " + ex.getMessage()); } } // ------------------------------------------------------------------ // dropTable // Attempt to drop a table. // // Grammar: // // drop-statement ::= DROP TABLE table-name // // ------------------------------------------------------------------ protected void dropTable() throws SQLException { // The SQL statement must have 3 elements: // // DROP TABLE table if (parsedSQL.length != 3) { throw new SQLException ("Invalid DROP statement"); } // The next word must be TABLE; this is the only type of // DROP that the SimpleText driver supports. if (!parsedSQL[1].equalsIgnoreCase("TABLE")) { throw new SQLException("DROP must be followed by TABLE"); } // Make sure we are not in read-only mode if (ownerConnection.isReadOnly()) { throw new SQLException( "Unable to DROP TABLE: connection is read-only"); } // The next word is the table name. Verify that it does not // contain any invalid characters. validateName(parsedSQL[2], "table"); // Verify that the file exists String fileName = parsedSQL[2].toUpperCase(); String fullFile = fileName + SimpleTextDefine.DATA_FILE_EXT; String fullPath = ownerConnection.getCatalog() + "/" + fullFile; File f = new File (fullPath); if (!f.exists()) { throw new SQLException("Table does not exist: " + fileName); } // Delete the file f.delete(); // If a binary data file exists, delete it now fullFile = fileName + SimpleTextDefine.BINARY_FILE_EXT; fullPath = ownerConnection.getCatalog() + "/" + fullFile; f = new File (fullPath); if (f.exists()) { f.delete(); } } // ------------------------------------------------------------------ // insert // Attempt to insert data into a table. // // Grammar: // // insert-statement ::= INSERT INTO table-name // [(column-identifier [,column- // identifier]...)] // VALUES (insert-value [,insert- // value]...) // // ------------------------------------------------------------------ protected void insert( boolean prepareOnly) throws SQLException { // The SQL statement must have at least 7 elements: // // INSERT INTO table VALUES (value) if (parsedSQL.length <= 7) { throw new SQLException ("Invalid INSERT statement"); } // The next word must be INTO if (!parsedSQL[1].equalsIgnoreCase("INTO")) { throw new SQLException("INSERT must be followed by INTO"); } // Make sure we are not in read-only mode if (ownerConnection.isReadOnly()) { throw new SQLException( "Unable to INSERT: connection is read-only"); } // The next word is the table name. Verify that it does not // contain any invalid characters. String tableName = parsedSQL[2]; validateName(tableName, "table"); // Verify that the file exists. If getColumns returns null, // the table does not exist. Hashtable columnList = ownerConnection.getColumns( ownerConnection.getCatalog(), tableName); if (columnList == null) { throw new SQLException("Table does not exist: " + tableName); } int pos = 3; Hashtable insertList = null; Hashtable valueList = null; int colNo = 1; SimpleTextColumn column; SimpleTextColumn column2; String name; // If the next word is a paren '(', the column names are being // specified. Build a list of columns that will have data // inserted. if (parsedSQL[pos].equals("(")) { insertList = new Hashtable(); pos++; if (pos >= parsedSQL.length) { throw new SQLException ("Invalid INSERT statement"); } // Build our insert list. Get each comma separated name // until we read a close paren. pos = buildList(parsedSQL, pos, ")", insertList); // Make sure at least one column was given if (insertList.size() == 0) { throw new SQLException ("No columns given"); } // Now that we have the insert list, verify each name is in // our target table and get the type and precision for (int i = 1; i <= insertList.size(); i++) { column = (SimpleTextColumn) insertList.get(new Integer(i)); column2 = findColumn(columnList, column.name); if (column2 == null) { throw new SQLException("Column does not exist: " + column.name); } column.type = column2.type; column.precision = column2.precision; } // Position to the next word after the closing paren pos++; if (pos >= parsedSQL.length) { throw new SQLException( "Invalid INSERT statement; missing VALUES clause"); } } // The next word is VALUES; no column list was given, so assume // all columns in the table. else if (parsedSQL[pos].equalsIgnoreCase("VALUES")) { insertList = new Hashtable(); // Build the insertList with all columns in the table for (colNo = 1; colNo <= columnList.size(); colNo++) { column2 = (SimpleTextColumn)columnList.get(new Integer(colNo)); if (column2 == null) { throw new SQLException("Invalid column number: " + colNo); } column = new SimpleTextColumn(column2.name); column.type = column2.type; column.precision = column2.precision; insertList.put(new Integer(colNo), column); } } else { // Invalid SQL statement throw new SQLException( "Invalid INSERT statement, no VALUES clause"); } // The next word must be VALUES. If there was an insert list, // we have positioned past it. if (!parsedSQL[pos].equalsIgnoreCase("VALUES")) { throw new SQLException( "Invalid INSERT statement; missing VALUES clause"); } pos++; if (pos >= parsedSQL.length) { throw new SQLException ( "Invalid INSERT statement, missing values"); } // The next word must be the open paren that starts the values if (!parsedSQL[pos].equals("(")) { throw new SQLException ( "Invalid INSERT statement, missing values"); } pos++; if (pos >= parsedSQL.length) { throw new SQLException ( "Invalid INSERT statement, missing values"); } // Build our value list. Get each comma separated value until // we read a close paren. valueList = new Hashtable(); pos = buildList(parsedSQL, pos, ")", valueList); // We could check for junk after the INSERT statement, but we // won't. // Verify that the number of insert items matches the number // of data items. if (insertList.size() != valueList.size()) { throw new SQLException("Number of values does not equal the number of items in the insert list"); } // Verify the data is correct validateData(insertList, valueList, prepareOnly); // If we are just preparing the statement, exit now if (prepareOnly) { return; } // Now we can build the line that will get written to the // simple text file. If there is any binary data, write it first // so that we know what the offset will be. String sdfPath = ownerConnection.getCatalog() + "/" + tableName + SimpleTextDefine.DATA_FILE_EXT; String sbfPath = ownerConnection.getCatalog() + "/" + tableName + SimpleTextDefine.BINARY_FILE_EXT; File sdf = new File(sdfPath); File sbf = new File(sbfPath); RandomAccessFile rafsdf = null; RandomAccessFile rafsbf = null; if (!sdf.exists()) { throw new SQLException("Text file does not exist: " + sdfPath); } String line = ""; long binaryPos = 0; for (int i = 1; i <= columnList.size(); i++) { column2 = (SimpleTextColumn) columnList.get(new Integer(i)); // Separate the data by a comma if (i > 1) { line += ","; } // If there is no data for this column, skip it colNo = findColumnNumber(insertList, column2.name); if (colNo == 0) { // No data, put in defaults switch(column2.type) { case Types.VARCHAR: line += "''"; break; case Types.VARBINARY: line += "-1"; break; default: line += "0"; break; } continue; } column = (SimpleTextColumn) valueList.get(new Integer(colNo)); if (column2.type == Types.VARBINARY) { if (rafsbf == null) { if (!sbf.exists()) { throw new SQLException("Binary file does not exist: " + sbfPath); } try { rafsbf = new RandomAccessFile(sbf, "rw"); // Position to the end of file rafsbf.seek(rafsbf.length()); } catch (Exception ex) { throw new SQLException("Unable to access " + sbfPath + ": " + ex.getMessage()); } } try { // Get the current position binaryPos = rafsbf.getFilePointer(); // Create a new CommonValue with the hex digits // (remove the quotes). CommonValue value = new CommonValue( column.name.substring(1, column.name. length() - 1)); // Now let CommonValue convert the hex string into // a byte array. byte b[] = value.getBytes(); // Write the length first rafsbf.writeInt(b.length); // Write the data rafsbf.write(b); } catch (Exception ex) { throw new SQLException("Unable to access " + sbfPath + ": " + ex.getMessage()); } // Put the offset pointer in the line line += binaryPos; } // Else some kind of text data, put directly in the line else { line += column.name; } } // If the binary file was opened, close it now if (rafsbf != null) { try { rafsbf.close(); } catch (Exception ex) { throw new SQLException("Unable to close " + sbfPath + ": " + ex.getMessage()); } } // Now that we have the data line, write it out to the text // file. try { rafsdf = new RandomAccessFile(sdf, "rw"); // Position to the end of file rafsdf.seek(rafsdf.length()); rafsdf.writeBytes(line); rafsdf.writeBytes("\n"); rafsdf.close(); } catch (Exception ex) { throw new SQLException("Unable to access " + sdfPath + ": " + ex.getMessage()); } } // ------------------------------------------------------------------ // select // Select data from a table. // // Grammar: // // select-statement ::= SELECT select-list FROM table-name // [WHERE search-condition] // // select-list ::= * | column-identifier [,column-identifier]... // search-condition ::= column-identifier comparison-operator literal // comparison-operator ::= < | > | = | <> // // ------------------------------------------------------------------ protected void select( boolean prepareOnly) throws SQLException { // Initialize the filter object resultSetFilter = null; // The SQL statement must have at least 4 elements: // // SELECT * FROM table if (parsedSQL.length < 4) { throw new SQLException ("Invalid SELECT statement"); } Hashtable selectList = new Hashtable(); int pos = 1; // Build our select list. Get each comma separated name until // we read a 'FROM'. pos = buildList(parsedSQL, pos, "FROM", selectList); // There must be at least one column if (selectList.size() == 0) { throw new SQLException("Select list must be specified"); } // Increment past the 'FROM' word. This is the table name pos++; if (pos >= parsedSQL.length) { throw new SQLException("Missing table name"); } // The next word is the table name. Verify that it does not // contain any invalid characters. String tableName = parsedSQL[pos]; validateName(tableName, "table"); // Verify that the file exists. If getColumns returns null, // the table does not exist. Hashtable columnList = ownerConnection.getColumns( ownerConnection.getCatalog(), tableName); if (columnList == null) { throw new SQLException("Table does not exist: " + tableName); } // Now go back through the select list and verify that each // column specified is contained in the table. Also expand // any * to be all columns. Hashtable validList = new Hashtable(); int validCount = 0; SimpleTextColumn column; SimpleTextColumn column2; for (int i = 1; i <= selectList.size(); i++) { // Get the next column from the select list column = (SimpleTextColumn) selectList.get(new Integer(i)); // If it's an *, expand it to all columns in the table if (column.name.equals("*")) { for (int j = 1; j <= columnList.size(); j++) { column2 = (SimpleTextColumn)columnList.get(new Integer(j)); validCount++; validList.put(new Integer(validCount), column2); } } else { // Make sure the column exists in the table column2 = findColumn(columnList, column.name); if (column2 == null) { throw new SQLException("Column not found: " + column.name); } // Put column on our valid list validCount++; validList.put(new Integer(validCount), column2); } } // Now we know the table exists and have a list of valid columns. // Process the WHERE clause if one exists. pos++; if (pos < parsedSQL.length) { // The next word should be WHERE if (!parsedSQL[pos].equalsIgnoreCase ("WHERE")) { throw new SQLException("WHERE clause expected"); } // Create a filter object resultSetFilter = new SimpleTextFilter(); pos++; if (pos >= parsedSQL.length) { throw new SQLException( "Column name expected after WHERE clause"); } // The next word is a column name. Make sure it exists in // the table. resultSetFilter.column = findColumn(columnList, parsedSQL[pos]); if (resultSetFilter.column == null) { throw new SQLException("Column not found: " + parsedSQL[pos]); } // Make sure the column is searchable if (!resultSetFilter.column.searchable) { throw new SQLException( "Column is not searchable: " + parsedSQL[pos]); } pos++; // The next word is the operator. Some operators may take // 2 words (i.e <>). if (pos >= parsedSQL.length) { throw new SQLException("Operator expected in WHERE clause"); } if (parsedSQL[pos].equals("=")) { resultSetFilter.operator = SimpleTextFilter.OP_EQ; } else if (parsedSQL[pos].equals("<")) { resultSetFilter.operator = SimpleTextFilter.OP_LT; } else if (parsedSQL[pos].equals(">")) { resultSetFilter.operator = SimpleTextFilter.OP_GT; } else { throw new SQLException("Invalid operator: " + parsedSQL[pos]); } // The next word may be our value, or it may be the second // part of an operator. pos++; if (pos >= parsedSQL.length) { throw new SQLException("Value expected in WHERE clause"); } if ((resultSetFilter.operator == SimpleTextFilter.OP_LT) && (parsedSQL[pos].equals(">"))) { resultSetFilter.operator = SimpleTextFilter.OP_NE; pos++; if (pos >= parsedSQL.length) { throw new SQLException("Value expected in WHERE clause"); } } // Get the data value and validate Hashtable whereList = new Hashtable(); Hashtable dataList = new Hashtable(); column = new SimpleTextColumn(parsedSQL[pos]); whereList.put(new Integer(1), resultSetFilter.column); dataList.put(new Integer(1), column); validateData(whereList, dataList, prepareOnly); String s = parsedSQL[pos]; // validateData could have massaged the data value (such as // in executing a prepared statement with parameters). Get // the value back. s = ((SimpleTextColumn) dataList.get(new Integer(1))).name; // Strip off any quotes if (s.startsWith("'") && s.endsWith("'")) { s = s.substring(1,s.length() - 1); } resultSetFilter.value = new CommonValue(s); pos++; // Check for extra junk at the end of the statement if (pos < parsedSQL.length) { throw new SQLException( "Invalid characters following WHERE clause"); } } // Set the catalog name, table name, and column Hashtable for // the result set. resultSetCatalog = ownerConnection.getCatalog(); resultSetTable = tableName; resultSetColumns = validList; } // ------------------------------------------------------------------ // findColumn // Given a SimpleTextColumn Hashtable and a column name, return // the SimpleTextColumn that matches. Null if no match. The column // numbers are 1-based. // ------------------------------------------------------------------ protected SimpleTextColumn findColumn( Hashtable list, String name) { SimpleTextColumn column; for (int i = 1; i <= list.size(); i++) { column = (SimpleTextColumn) list.get(new Integer(i)); if (column != null) { if (column.name.equalsIgnoreCase(name)) { return column; } } } return null; } // ------------------------------------------------------------------ // findColumnNumber // Given a SimpleTextColumn Hashtable and a column name, return // the column number that matches. 0 if no match. The column // numbers are 1-based. // ------------------------------------------------------------------ protected int findColumnNumber( Hashtable list, String name) { SimpleTextColumn column; for (int i = 1; i <= list.size(); i++) { column = (SimpleTextColumn) list.get(new Integer(i)); if (column != null) { if (column.name.equalsIgnoreCase(name)) { return i; } } } return 0; } // ------------------------------------------------------------------ // buildList // Given a parsed SQL statement, the current position, and the ending // word, build a list of the comma separated words from the SQL // statement. This is used for the insert column list, insert // values, and select list. Returns the new position in the parsed // SQL. // ------------------------------------------------------------------ public int buildList( String sql[], int pos, String endWord, Hashtable list) throws SQLException { SimpleTextColumn column; boolean done = false; String name; int colNo = 1; // Loop while more data is present while (!done) { // Get the next column name = sql[pos]; column = new SimpleTextColumn(name); list.put(new Integer(colNo), column); colNo++; pos++; if (pos >= sql.length) { if (endWord.length() > 0) { throw new SQLException ( "Invalid statement after " + name); } else { done = true; break; } } // If the next word is not a comma, it must be our ending // word. if (!sql[pos].equals(",")) { // Found the ending word? exit the loop if (sql[pos].equalsIgnoreCase(endWord)) { done = true; break; } if (endWord.length() == 0) { throw new SQLException("Invalid data format"); } throw new SQLException ( "Invalid statement after " + name); } pos++; if (pos >= sql.length) { if (endWord.length() > 0) { throw new SQLException ( "Invalid statement after " + name); } else { done = true; break; } } } return pos; } // ------------------------------------------------------------------ // validateData // Given an insert list and a data list, verify that each data // element is proper for the given type and precision. // ------------------------------------------------------------------ protected void validateData( Hashtable insertList, Hashtable dataList, boolean prepareOnly) throws SQLException { SimpleTextColumn insert; SimpleTextColumn data; int precision = 0; int paramNum = 0; // Init number of parameters if we are preparing if (prepareOnly) { paramCount = 0; } for (int i = 1; i <= insertList.size(); i++) { insert = (SimpleTextColumn) insertList.get(new Integer(i)); data = (SimpleTextColumn) dataList.get(new Integer(i)); // If a parameter marker is found, either continue to the // next list item because we are preparing, or replace it // with a bound parameter value. if (data.name.equals("?")) { if (prepareOnly) { // Increment number of parameter markers paramCount++; continue; } // Increment current parameter number paramNum++; // Get String value for the bound parameter from the // boundParams Hashtable. If it is not found, throw // an exception indicating that not all of the parameters // have been set. if (boundParams != null) { String s = (String) boundParams.get(new Integer(paramNum)); if (s == null) { throw new SQLException( "Not all parameters have been set"); } // Set the value into the SimpleTextColumn entry // If the data is a string or binary type, enclose it // in quotes. switch(insert.type) { case Types.VARCHAR: case Types.VARBINARY: data.name = "'" + s + "'"; break; default: data.name = s; break; } } } switch(insert.type) { case Types.VARCHAR: if (!data.name.startsWith("'") || (data.name.length() < 2) || !data.name.endsWith("'")) { throw new SQLException( "String data must be enclosed in single quotes: " + data.name); } precision = data.name.length() - 2; break; case Types.INTEGER: try { Integer.valueOf(data.name); } catch (Exception ex) { throw new SQLException("Invalid numeric data: " + data.name); } precision = data.name.length(); break; case Types.BINARY: if (!data.name.startsWith("'") || (data.name.length() < 2) || !data.name.endsWith("'")) { throw new SQLException( "Binary data must be enclosed in single quotes: " + data.name); } if ((data.name.length() % 2) != 0) { throw new SQLException( "Binary data must have even number of hex digits:" + data.name); } precision = (data.name.length() - 2) / 2; break; } if (precision > insert.precision) { throw new SQLException("Invalid data precision for " + insert.name); } } } // ------------------------------------------------------------------ // validateName // Verify that the given name does not contain any invalid // characters. This will be used for both table names and column // names. // ------------------------------------------------------------------ protected void validateName( String name, String type) throws SQLException { // Invalid characters other than a-z, 0-9, and A-Z String invalid = "@#./\\()"; char c; int j; for (int i = 0; i < name.length(); i++) { c = name.charAt(i); // If it's not an alpha numeric or numeric character, // check the list of invalid characters if (!((c >= 'a') && (c <= 'z')) && !((c >= '0') && (c <= '9')) && !((c >= 'A') && (c <= 'Z'))) { for (j = 0; j < invalid.length(); j++) { if (c == invalid.charAt(j)) { throw new SQLException("Invalid " + type + " name: " + name); } } } } } //------------------------------------------------------------------- // getConnection // Returns the owner connection object. //------------------------------------------------------------------- public SimpleTextIConnection getConnection() { return ownerConnection; } // Owning connection object protected SimpleTextIConnection ownerConnection; // SQLWarning chain protected SQLWarning lastWarning; // The current SQL statement protected String sqlStatement; // The String array of parsed SQL words protected String parsedSQL[]; // The current SQL statement type (i.e. SQL_SELECT, SQL_CREATE, etc.) protected int statementType; // Update count for the last statement that executed protected int updateCount; // Attributes used for creating a result set String resultSetCatalog; String resultSetTable; Hashtable resultSetColumns; // If a filter exists for a select statement, a SimpleTextFilter // object will be created. SimpleTextFilter resultSetFilter; // Our current result set ResultSet currentResultSet; // A Hashtable for each bound parameter. Only valid for // PreparedStatements. Hashtable boundParams; // The count of parameter markers. Only valid for PreparedStatements int paramCount; }
Table of Contents |